#!/usr/bin/env python
# -*- coding: utf-8 -*-
# 时间     : 2018-06-09 16:08:16
# 作者     : DL (584054432@qq.com)
# 网址     : https://gitee.com/dingliangPython/
# 软件版本 : Python3.6.5
# 功能     ：

import logging
import os
import sqlite3
import sys
import cx_Oracle
import pymssql

import pymysql
import pyodbc
import re
import time_ext
from check_local_database import CheckTable

if sys.version_info.major == 3 and sys.version_info.minor <= 4:
    from DBUtils.PooledDB import PooledDB
else:
    from dbutils.pooled_db import PooledDB

if pymssql.__version__ >= '2.2.1':
    from pymssql import _mssql
else:
    import _mssql

os.environ['NLS_LANG'] = 'AMERICAN_AMERICA.ZHS16GBK'


class Base(object):
    """docstring for Base"""

    def __init__(self, *args, **kwargs):
        super(Base, self).__init__()
        self.args = args
        self.kwargs = kwargs
        self.log = logging.getLogger("main.connect_database_base")

    # 查询数据
    def loadSql(self, sql, result_dict = None):
        try:

            conn = self.get_connect()
            cursor = conn.cursor()
            cursor.execute(sql)
            value = cursor.fetchall()
            data = []
            if value and len(value) >= 1:
                # 返回元组
                if not result_dict:
                    return value
                # 返回字典
                field = [d[0] for d in cursor.description]
                for v in value:
                    data.append(dict(zip(field, v)))
                conn.commit()
                cursor.close()
                conn.close()
                return data
            else:
                conn.commit()
                cursor.close()
                conn.close()
                return 0
        except Exception as e:
            if "NoneType" in str(e):
                self.log.error(
                        "连接:{},主机:{},ODBC:{},库/实/或文件:{},连接失败,请检测连接".format(self.name, self.host, self.dsn,
                                                                           self.database))
                return
            if e.args and isinstance(e.args[0], str) and not len(e.args[0].split(':')) > 1:
                self.log.error("查询出错:{},出错语句:{}".format(e, sql))
                return
            if e.args and isinstance(e.args[0], str):
                errinfo, field = e.args[0].split(':')
                table_name = self.get_table_name(sql)
                if errinfo != "no such column":
                    self.log.warning("异常SQL : {},异常:{}".format(sql, e))

                if not table_name:
                    self.log.warning("异常SQL : {},异常:{}".format(sql, e))

                else:
                    CheckTable.add_field(self, table_name, field)
                    self.loadSql(sql, result_dict)

            else:
                self.log.warning("异常SQL : {},异常:{}".format(sql, e))

    # 执行 插入，更新，删除等操作语句
    def execSql(self, sql, value_list = None):
        try:
            conn = self.get_connect()
            cursor = conn.cursor()
            if not value_list:
                cursor.execute(sql)
                result = cursor.rowcount
                conn.commit()
                cursor.close()
                conn.close()
                return result
            else:

                cursor.executemany(sql, value_list)

                result = cursor.rowcount
                conn.commit()
                cursor.close()
                conn.close()
                return result
        except Exception as e:
            errorInfo = str(e)
            if "ORA-00001" in errorInfo or "UNIQUE constraint failed" in errorInfo:
                self.log.error("主键冲突,请检测数据是否已存在...,SQL={},value={}".format(sql, value_list))
                return
            if "NoneType" in errorInfo:
                self.log.error(
                        "连接:{},主机:{},ODBC:{},库/实/或文件:{},连接失败,请检测连接".format(self.name, self.host, self.dsn,
                                                                           self.database))
                return
            if "PRIMARY KEY" in errorInfo:
                self.log.error("已存在数据,主键冲突..不能插入数据,sql={},value={},异常:{}".format(sql, value_list, errorInfo))
                return
            if e.args and isinstance(e.args[0], str) and not len(e.args[0].split(':')) > 1:
                self.log.error("查询出错:{},出错语句:{}".format(e, sql))
                return
            if e.args and isinstance(e.args[0], str):
                errinfo, field = e.args[0].split(':')
                table_name = self.get_table_name(sql)
                if errinfo != "no such column":
                    self.log.warning("异常SQL : {},异常:{},value= {} ".format(sql, e, value_list))

                if not table_name:
                    self.log.warning("异常SQL : {},异常:{},value= {} ".format(sql, e, value_list))

                else:
                    CheckTable.add_field(self, table_name, field)
                    self.execSql(sql, value_list)

            else:
                self.log.warning("异常SQL : {},异常:{},value= {} ".format(sql, e, value_list))
            # self.log.error("执行出错:{},出错语句:{},{}".format(errorInfo, sql, value_list))

    # 获取出差sql表名
    def get_table_name(self, sql):
        split_list = re.findall('FROM\s+(\w+)', sql, re.I)
        if split_list:
            return split_list[0]
        split_list = re.findall('UPDATE\s+(\w+)', sql, re.I)
        if split_list:
            return split_list[0]
        split_list = re.findall('DELETE\s+(\w+)', sql, re.I)
        if split_list:
            return split_list[0]

    # 获取占位符号
    def get_placeholder(self, field_list):
        if self._type == "Oracle":
            return [":{}".format(i) for i in enumerate(field_list)]
        if self._type in ["Sqlite", 'Access']:
            return ["?" for _ in field_list]
        if self._type in ("SQLServer", "Mysql"):
            return ["%s" for _ in field_list]


class Connect(Base):

    def __init__(self, **kwargs):
        super(Connect, self).__init__()
        self.__dict__ = kwargs
        self.log = logging.getLogger("main.connect_database")
        self.kwargs, self.driver = self.get_connect_info()
        self.pool_connect = self.connect_pool()
        self.load_sql = self.loadSql
        self.exec_sql = self.execSql

    # 默认没有的属性为None
    def __getattr__(self, key):
        return None

    # 获取连接信息
    def get_connect_info(self):
        kwargs = {'user': self.username, 'password': self.password}
        info = {'host': self.host, 'port': self.port, 'database': self.database}
        if self.pool:
            kwargs.update({
                    'maxconnections': self.maxconnections, 'mincached': self.mincached,
                    'maxcached': self.maxcached, 'maxshared': self.maxshared
                    })
        database_type = self.type.lower()
        if database_type == 'mysql':
            kwargs.update(info)
            kwargs.update({'charset': 'utf8', 'connect_timeout': 1})
            return kwargs, pymysql
        if database_type == 'oracle':
            dsn = {
                    'dsn': """
(DESCRIPTION =
   (ADDRESS = (PROTOCOL = TCP)(HOST = {host})(PORT = {port}))
   (CONNECT_DATA =(SID = {database} ) )
   (INBOUND_CONNECT_TIMEOUT_listener_{database}=1)
   (SQLNET.INBOUND_CONNECT_TIMEOUT = 2)
 )""".format(**info)
                    }
            kwargs.update(dsn)
            return kwargs, cx_Oracle
        if database_type in ('mssql', 'sqlserver', 'sql server'):
            kwargs.update(info)
            kwargs.update({'charset': 'utf8', 'login_timeout': 1})
            return kwargs, pymssql
        else:
            return kwargs, pyodbc

    # 获取连接池连接
    def get_pool_connect(self):
        if self.pool_connect:
            return self.pool_connect.connection()

    # 获取连接
    def get_connect(self):
        database_type = self.type.lower()
        # 短连接
        if database_type == 'access':
            return self.access_connect()
        # 短连接
        if database_type == 'sqlite':
            return sqlite3.connect(self.databasepath, check_same_thread = False)
        # 短连接
        if self.dsn:
            return self.dsn_connect()
        # 长连接
        if self.pool:
            return self.get_pool_connect()
        # 短连接
        return self.driver.connect(**self.kwargs)

    # 连接连接池
    def connect_pool(self):
        if not self.pool:
            return {}
        try:
            pool = PooledDB(creator = self.driver, blocking = True, **self.kwargs)
            self.log.info('连接:{},连接池初始成功'.format(self.name))
            return pool
        except Exception as e:
            self.log.warning("连接:{},数据库连接池失败,异常:{}".format(self.name, e))

    def rollback(self):
        self.get_connect().rollback()

    # odbc连接
    def dsn_connect(self):
        if self.type.lower() == 'oracle':
            return self.driver.connect(**self.kwargs)
        self.username = 'admin' if self.type.lower() == 'access' else self.username
        dsn = "DSN={};UID={};PWD={};".format(self.dsn, self.username, self.password or '')
        connect = pyodbc.connect(dsn, autocommit = True, timeout = 1)
        return connect

    # access连接
    def access_connect(self):
        if self.dsn:
            return self.dsn_connect()
        database = self.get_access_path()
        print("数据库:{}".format(database))
        ext = os.path.splitext(database)[1].lower()
        if ext == '.mdb':
            driver_str = 'Microsoft Access Driver (*.mdb)'
        else:
            driver_str = 'Microsoft Access Driver (*.mdb, *.accdb)'
        connect_str = "DRIVER={%s};DBQ=%s;Pwd=%s;ExtendedAnsiSQL=1;" % (driver_str, database, self.password or "")
        return pyodbc.connect(connect_str, timeout = 1, autocommit = True, )

    def get_access_path(self):
        path = self.databasepath or self.database
        file_path, file_name = os.path.split(path)

        ext = ""
        if self.format:
            now = time_ext.get_datetime()
            y = "{:0>4}".format(now.year)
            m = "{:0>2}".format(now.month)
            d = "{:0>2}".format(now.day)
            ext = self.format.replace('Y', y).replace('m', m).replace('d', d)
        size = len(ext)
        if self.left != '0':
            file_name = file_name[size:]
        if self.right != '0':
            name, _ext = os.path.splitext(file_name)
            name = name[:len(name) - size]
            file_name = name + _ext

        if ext and self.left != '0':
            path = "{}/{}{}".format(file_path, ext, file_name)
            return path

        if ext and self.right != '0':
            name, _ext = os.path.splitext(file_name)
            path = "{}/{}{}{}".format(file_path, name, ext, _ext)
            return path

        return path


class Obj(object):
    """docstring for obj"""

    def __init__(self, **kwargs):
        self.__dict__ = kwargs


def get_connect_list(config):
    connect_dict = {}
    config_obj = config.items() if isinstance(config, dict) else config
    for v in config_obj:
        name = v.get('id') or v.get('name')
        _type = v.get('type')
        if name and _type:
            connect_dict.update({name: Connect(**v)})
    return connect_dict